package br.ufrj.neg.evento.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import br.ufrj.neg.evento.bean.CustoContatoEventoBean;
import br.ufrj.neg.evento.bean.CustoGeralBean;
import br.ufrj.neg.model.ServiceLocator;

public class CustosDAO {


	public static List<CustoContatoEventoBean> loadParticipantes(Integer idEvento){
		
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<CustoContatoEventoBean> custos = new ArrayList<CustoContatoEventoBean>();
		CustoContatoEventoBean cb;
		
		try{
			String sql = "SELECT ec.*,c.* FROM evento_contatos ec INNER JOIN contatos c ON ec.cont_cd_id = c.cont_cd_id WHERE ec.even_cd_id = ? ORDER BY c.cont_ds_nome ASC";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idEvento);

			rs = ps.executeQuery();

			while(rs.next()){
				cb = new CustoContatoEventoBean(rs.getInt("even_cd_id"),rs.getInt("cont_cd_id"));
				cb.setDeslocamento(rs.getDouble("evco_cd_deslocamento"));
				cb.setHospedagem(rs.getDouble("evco_cd_hospedagem"));
				cb.setValorHora(rs.getDouble("evco_cd_valor_hora"));
				cb.setNome(rs.getString("cont_ds_nome"));
				
				custos.add(cb);
			}
			
			return custos;
			
		}catch(SQLException ex){
		
			ex.printStackTrace();
			return null;
			
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}

		
	}
	
public static List<CustoGeralBean> loadCustosGerais(Integer idEvento){
		
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<CustoGeralBean> custos = new ArrayList<CustoGeralBean>();
		CustoGeralBean cb;
		
		try{
			String sql = "SELECT * FROM custos_gerais WHERE even_cd_id = ? ORDER BY cuge_ds_nome ASC";
			
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idEvento);

			rs = ps.executeQuery();

			while(rs.next()){
				cb = new CustoGeralBean(rs.getInt("cuge_cd_id"),rs.getString("cuge_ds_nome"),rs.getInt("even_cd_id"),rs.getDouble("cuge_cd_valor"));
				custos.add(cb);
			}
			
			return custos;
			
		}catch(SQLException ex){
		
			ex.printStackTrace();
			return null;
			
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}

		
	}

	public static void saveParticipantes(Collection<CustoContatoEventoBean> custos) {
		// TODO Auto-generated method stub
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			conn.setAutoCommit(false);
			for (CustoContatoEventoBean custoContatoEventoBean : custos) {
				String sql = "UPDATE evento_contatos SET evco_cd_deslocamento = ?, evco_cd_hospedagem =? , evco_cd_valor_hora = ? " +
						"WHERE even_cd_id = ? AND cont_cd_id = ?";
				
				ps = conn.prepareStatement(sql);
				ps.setDouble(1, custoContatoEventoBean.getDeslocamento());
				ps.setDouble(2, custoContatoEventoBean.getHospedagem());
				ps.setDouble(3, custoContatoEventoBean.getValorHora());
				ps.setInt(4, custoContatoEventoBean.getIdEvento());
				ps.setInt(5, custoContatoEventoBean.getIdContato());
				
				ps.executeUpdate();

			}
			conn.commit();

		}catch(SQLException ex){
		
			ex.printStackTrace();
			
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}
	}
		
	public static void saveCustosGerais(Collection<CustoGeralBean> custos, Integer idEvento){
		// TODO Auto-generated method stub
		Connection conn = ServiceLocator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try{
			conn.setAutoCommit(false);
			String sql = "DELETE FROM custos_gerais WHERE even_cd_id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, idEvento);
			ps.executeUpdate();
			
			
			for (CustoGeralBean custoGeralBean : custos) {
				sql = "INSERT INTO custos_gerais " +
						"(cuge_cd_id,cuge_ds_nome,even_cd_id,cuge_cd_valor) " +
						"VALUES (DEFAULT,?,?,?)";
				
				ps = conn.prepareStatement(sql);
				ps.setString(1, custoGeralBean.getNome());
				ps.setInt(2, custoGeralBean.getIdEvento());
				ps.setDouble(3, custoGeralBean.getCusto());
				ps.executeUpdate();

			}
			conn.commit();

		}catch(SQLException ex){
		
			ex.printStackTrace();
			
		}finally{
			try {conn.close();} catch (SQLException e) {e.printStackTrace();}
			try {ps.close();} catch (SQLException e) {e.printStackTrace();}
		}

	}
			
}

